/***********************************************************************************************************************************
 * Name:	Script_Logins.sql
 * Author:	Frank Figearo (frank.figearo@us.bbaaviation.com|frank@sqlnerd.me)
 * Summary:	Script out server logins and role memberships.
 *	http://weblogs.sqlteam.com/billg/archive/2010/07/08/Scripting-out-SQL-Server-Logins.aspx
 */
SET IMPLICIT_TRANSACTIONS OFF; WHILE 0 < @@TRANCOUNT ROLLBACK;
GO
SET NOCOUNT ON;
PRINT N'/***********************************************************************************************************************************';
PRINT @@SERVERNAME;
PRINT CURRENT_TIMESTAMP;
PRINT N'*/';
PRINT N'';

-- Active Directory Logins
SELECT [-- Active Directory Logins]= N'IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE name = ''' + name + N''')
	CREATE LOGIN [' + name + N'] FROM WINDOWS WITH DEFAULT_DATABASE = [' + default_database_name + N'], DEFAULT_LANGUAGE = ['+default_language_name + N'];	-- ' + type_desc COLLATE DATABASE_DEFAULT
  FROM master.sys.server_principals
  WHERE type IN (N'G', N'U') AND name NOT LIKE N'BUILTIN%' AND name NOT LIKE N'NT AUTHORITY%' AND name NOT LIKE N'%\SQLServer%'
  ORDER BY type_desc, name;

-- SQL Server Logins
SELECT [-- SQL Server Logins]= N'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE name = ''' + name + N''')
	CREATE LOGIN [' + name + N'] WITH PASSWORD = ' + sys.fn_varbintohexstr(password_hash) + N' HASHED, SID = ' + sys.fn_varbintohexstr([sid]) + N', DEFAULT_DATABASE = [' + default_database_name + N'], DEFAULT_LANGUAGE = [' + default_language_name + N'], CHECK_EXPIRATION = ' + CASE is_expiration_checked WHEN 1 THEN N'ON' ELSE N'OFF' END + N', CHECK_POLICY = ' + CASE is_policy_checked WHEN 1 THEN N'ON' ELSE N'OFF' END + ';'
  FROM master.sys.sql_logins
  WHERE name NOT IN (N'sa', N'guest')
  ORDER BY name;

-- Disabled Logins
SELECT [-- Disabled Logins]= N'ALTER LOGIN [' + name + N'] DISABLE;' 
  FROM master.sys.server_principals 
  WHERE is_disabled = 1
  ORDER BY name;

-- Role Membership
SELECT [-- Role Membership]= N'EXECUTE master.dbo.sp_addsrvrolemember @loginame = N''' + l.name + N''', @rolename = N''' + r.name + N''';'
  FROM sys.server_principals r
	INNER JOIN sys.server_role_members rm ON (r.principal_id = rm.role_principal_id)
	RIGHT JOIN sys.server_principals l ON (rm. member_principal_id = l.principal_id)
  WHERE l.[type] <> 'R' AND r.name IS NOT Null
	AND l.name NOT IN (N'sa') AND l.name NOT LIKE N'BUILTIN%' and l.name NOT LIKE N'NT AUTHORITY%' and l.name NOT LIKE N'%\SQLServer%'
  ORDER BY l.name;

SET NOCOUNT OFF;
GO